import pymysql
import pandas as pd
import pymysql.cursors

class MysqlUtils(object):

    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='root',
            port=3306,
            charset='utf8'
        )
    def get_scenic_data(self):
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        SELECT order_id, id_no, user_name, phone, age,user_count FROM(SELECT o.id as order_id, u.id_no, u.user_name, u.phone,
CASE
     WHEN length(u.id_no)=18 THEN
           YEAR(now())-cast(substr(u.id_no, 7, 4) as signed)
      ELSE
         NULL
END as age;
(SELECT count(*)FORM ticket_order_user_rel WHERE order_id = o.id) as user_count
FROM ticket_order o JOIN ticket_order_user_rel u on u.order_id= o.id WHERE o.pay_time is not null and o.pay_time !='' and u.id_no is not null and u.id_no !=') as subquery WHERE user_count = 1 and(age< 18 or age>=60)
"""
    cursor.execute(sql)
    ret = cursor.fetchLL()
    df = pd.DataFrame(ret)
    print(df)
    df.to_csv('./outlier/scenic_data.csv')

if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()